/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package logic;

import com.smartxls.WorkBook;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import xlsBeans.HideColumn;
import xlsBeans.LineXLS;

/**
 *
 * @author allankv
 */

public class SpreadsheetHandling {
    private WorkBook w;
    private String url;
    private int sp = 110;
    
    public SpreadsheetHandling(String url){
        this.url = url;        
    }
    public SpreadsheetHandling(){
        this.url = "spreadsheetsync_model.xls";
        //this.url = "/var/www/bdd/images/uploaded/spreadsheetsync_model.xls";
        readFile();
    }
    private void readFile(){
        try {
            w = new WorkBook();
            // Verificar se é XLS ou XLSX
            //if(url.matches("xlsx$")){
            //    w.readXLSX(url);
            //}else{
                w.read(url);
            //}
        } catch (Exception ex) {
            Logger.getLogger(SpreadsheetHandling.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public List<LineXLS> readSpreadsheet(){
        this.readFile();
        List<LineXLS> lList = new ArrayList<LineXLS>();
        // Percorrer todas a linhas da tabela
        for(int i = 11; i <= this.w.getLastRow();i++){
            LineXLS l = new LineXLS(w, i);
            lList.add(l);
        }
        return lList;
    }


    public String writeSpreadsheet(List<LineXLS> lList, HideColumn h){
        h = h == null?new HideColumn():h;
        int l = 12;
        try {
            for(LineXLS aux:lList){
                    //Main Fields
                    w.setText("B"+l, aux.getSp1().getPrivate1()?"YES":"NO");
                    w.setText("C"+l, aux.getSp1().getMain().getBasisofrecord());
                    w.setText("D"+l,aux.getSp1().getMain().getInstitutioncode());
                    w.setText("E"+l,aux.getSp1().getMain().getCollectioncode());
                    w.setText("F"+l,aux.getSp1().getMain().getCatalognumber());
                    w.setText("G"+l,aux.getSp1().getMain().getScientificname());
                    //Record-Level Elements
                    w.setText("H"+l,aux.getSp1().getRec().getType());
                    w.setColHidden(7, h.isType()==1);
                    w.setColHidden(7 + sp, h.isType()==1);
                    w.setText("I"+l,aux.getSp1().getRec().getOwnercode());
                    w.setColHidden(8, h.isOwnerinstitution()==1);
                    w.setColHidden(8 + sp, h.isOwnerinstitution()==1);
                    w.setText("J"+l,aux.getSp1().getRec().getDataset());
                    w.setColHidden(9, h.isDataset()==1);
                    w.setColHidden(9 + sp, h.isDataset()==1);
                    w.setText("K"+l,aux.getSp1().getRec().getRights());
                    w.setColHidden(10, h.isRights()==1);
                    w.setColHidden(10 + sp, h.isRights()==1);
                    w.setText("L"+l,aux.getSp1().getRec().getRightsholder());
                    w.setColHidden(11, h.isRightsholder()==1);
                    w.setColHidden(11 + sp, h.isRightsholder()==1);
                    w.setText("M"+l,aux.getSp1().getRec().getAccessrights());
                    w.setColHidden(12, h.isAccessrights()==1);
                    w.setColHidden(12 + sp, h.isAccessrights()==1);
                    w.setText("N"+l,aux.getSp1().getRec().getInformationwithheld());
                    w.setColHidden(13, h.isInformationwithheld()==1);
                    w.setColHidden(13 + sp, h.isInformationwithheld()==1);
                    w.setText("O"+l,aux.getSp1().getRec().getDatageneralization());
                    w.setColHidden(14, h.isDatageneralization()==1);
                    w.setColHidden(14 + sp, h.isDatageneralization()==1);
                    w.setText("P"+l,aux.getSp1().getRec().getDynamicproperties());
                    w.setColHidden(15, h.isDynamicproperties()==1);
                    w.setColHidden(15 + sp, h.isDynamicproperties()==1);
                    //Taxonomic Elements
                    w.setText("Q"+l,aux.getSp1().getTax().getKingdom());
                    w.setColHidden(16, h.isKingdom()==1);
                    w.setColHidden(16 + sp, h.isKingdom()==1);
                    w.setText("R"+l,aux.getSp1().getTax().getPhylum());
                    w.setColHidden(17, h.isPhylum()==1);
                    w.setColHidden(17 + sp, h.isPhylum()==1);
                    w.setText("S"+l,aux.getSp1().getTax().getClasse());
                    w.setColHidden(18, h.isClasse()==1);
                    w.setColHidden(18 + sp, h.isClasse()==1);
                    w.setText("T"+l,aux.getSp1().getTax().getOrder());
                    w.setColHidden(19, h.isOrder()==1);
                    w.setColHidden(19 + sp, h.isOrder()==1);
                    w.setText("U"+l,aux.getSp1().getTax().getFamily());
                    w.setColHidden(20, h.isFamily()==1);
                    w.setColHidden(20 + sp, h.isFamily()==1);
                    w.setText("V"+l,aux.getSp1().getTax().getGenus());
                    w.setColHidden(21, h.isGenus()==1);
                    w.setColHidden(21 + sp, h.isGenus()==1);
                    w.setText("W"+l,aux.getSp1().getTax().getSubgenus());
                    w.setColHidden(22, h.isSubgenus()==1);
                    w.setColHidden(22 + sp, h.isSubgenus()==1);
                    w.setText("X"+l,aux.getSp1().getTax().getSpecificepithet());
                    w.setColHidden(23, h.isSpecificepithet()==1);
                    w.setColHidden(23 + sp, h.isSpecificepithet()==1);
                    w.setText("Y"+l,aux.getSp1().getTax().getInfraspecificepithet());
                    w.setColHidden(24, h.isInfraspecificepithet()==1);
                    w.setColHidden(24 + sp, h.isInfraspecificepithet()==1);
                    w.setText("Z"+l,aux.getSp1().getTax().getTaxonrank());
                    w.setColHidden(25, h.isTaxonrank()==1);
                    w.setColHidden(25 + sp, h.isTaxonrank()==1);
                    w.setText("AA"+l,aux.getSp1().getTax().getScientificnameauthor());
                    w.setColHidden(26, h.isScientificnameauthor()==1);
                    w.setColHidden(26 + sp, h.isScientificnameauthor()==1);
                    w.setText("AB"+l,aux.getSp1().getTax().getNomenclaturalcode());
                    w.setColHidden(27, h.isNomenclaturalcode()==1);
                    w.setColHidden(27 + sp, h.isNomenclaturalcode()==1);
                    w.setText("AC"+l,aux.getSp1().getTax().getTaxonconcept());
                    w.setColHidden(28, h.isTaxonconcept()==1);
                    w.setColHidden(28 + sp, h.isTaxonconcept()==1);
                    w.setText("AD"+l,aux.getSp1().getTax().getNomenclaturalstatus());
                    w.setColHidden(29, h.isNomenclaturalstatus()==1);
                    w.setColHidden(29 + sp, h.isNomenclaturalstatus()==1);
                    w.setText("AE"+l,aux.getSp1().getTax().getAcceptednameusage());
                    w.setColHidden(30, h.isAcceptednameusage()==1);
                    w.setColHidden(30 + sp, h.isAcceptednameusage()==1);
                    w.setText("AF"+l,aux.getSp1().getTax().getParentnameusage());
                    w.setColHidden(31, h.isParentnameusage()==1);
                    w.setColHidden(31 + sp, h.isParentnameusage()==1);
                    w.setText("AG"+l,aux.getSp1().getTax().getOriginalnameusage());
                    w.setColHidden(32, h.isOriginalnameusage()==1);
                    w.setColHidden(32 + sp, h.isOriginalnameusage()==1);
                    w.setText("AH"+l,aux.getSp1().getTax().getNameaccordingto());
                    w.setColHidden(33, h.isNameaccordingto()==1);
                    w.setColHidden(33 + sp, h.isNameaccordingto()==1);
                    w.setText("AI"+l,aux.getSp1().getTax().getNamepublishedin());
                    w.setColHidden(34, h.isNamepublishedin()==1);
                    w.setColHidden(34 + sp, h.isNamepublishedin()==1);
                    w.setText("AJ"+l,aux.getSp1().getTax().getVernacularname());
                    w.setColHidden(35, h.isVernacularname()==1);
                    w.setColHidden(35 + sp, h.isVernacularname()==1);
                    w.setText("AK"+l,aux.getSp1().getTax().getTaxonomicstatus());
                    w.setColHidden(36, h.isTaxonomicstatus()==1);
                    w.setColHidden(36 + sp, h.isTaxonomicstatus()==1);
                    w.setText("AL"+l,aux.getSp1().getTax().getVerbatimtaxonrank());
                    w.setColHidden(37, h.isVerbatimtaxonrank()==1);
                    w.setColHidden(37 + sp, h.isVerbatimtaxonrank()==1);
                    w.setText("AM"+l,aux.getSp1().getTax().getTaxonremarks());
                    w.setColHidden(38, h.isTaxonremarks()==1);
                    w.setColHidden(38 + sp, h.isTaxonremarks()==1);
                    //Occurrence Elements
                    w.setText("AN"+l,aux.getSp1().getOcc().getIndividual());
                    w.setColHidden(39, h.isIndividual()==1);
                    w.setColHidden(39 + sp, h.isIndividual()==1);
                    if(aux.getSp1().getOcc().getIndividualcount()!=null)
                        w.setNumber("AO"+l,aux.getSp1().getOcc().getIndividualcount());
                    w.setColHidden(40, h.isIndividualcount()==1);
                    w.setColHidden(40 + sp, h.isIndividualcount()==1);
                    w.setText("AP"+l,aux.getSp1().getOcc().getSex());
                    w.setColHidden(41, h.isSex()==1);
                    w.setColHidden(41 + sp, h.isSex()==1);
                    w.setText("AQ"+l,aux.getSp1().getOcc().getBehavior());
                    w.setColHidden(42, h.isBehavior()==1);
                    w.setColHidden(42 + sp, h.isBehavior()==1);
                    w.setText("AR"+l,aux.getSp1().getOcc().getLifestage());
                    w.setColHidden(43, h.isLifestage()==1);
                    w.setColHidden(43 + sp, h.isLifestage()==1);
                    w.setText("AS"+l,aux.getSp1().getOcc().getDisposition());
                    w.setColHidden(44, h.isDisposition()==1);
                    w.setColHidden(44 + sp, h.isDisposition()==1);
                    w.setText("AT"+l,aux.getSp1().getOcc().getReproductivecondition());
                    w.setColHidden(45, h.isReproductivecondition()==1);
                    w.setColHidden(45 + sp, h.isReproductivecondition()==1);
                    w.setText("AU"+l,aux.getSp1().getOcc().getEstabilishmentmean());
                    w.setColHidden(46, h.isEstabilishmentmean()==1);
                    w.setColHidden(46 + sp, h.isEstabilishmentmean()==1);
                    w.setText("AV"+l,aux.getSp1().getOcc().getRecordedby());
                    w.setColHidden(47, h.isRecordedby()==1);
                    w.setColHidden(47 + sp, h.isRecordedby()==1);
                    w.setText("AW"+l,aux.getSp1().getOcc().getRecordnumber());
                    w.setColHidden(48, h.isRecordnumber()==1);
                    w.setColHidden(48 + sp, h.isRecordnumber()==1);
                    w.setText("AX"+l,aux.getSp1().getOcc().getOthercatalognumbers());
                    w.setColHidden(49, h.isOthercatalognumbers()==1);
                    w.setColHidden(49 + sp, h.isOthercatalognumbers()==1);
                    w.setText("AY"+l,aux.getSp1().getOcc().getPreparations());
                    w.setColHidden(50, h.isPreparations()==1);
                    w.setColHidden(50 + sp, h.isPreparations()==1);
                    w.setText("AZ"+l,aux.getSp1().getOcc().getAssociatedsequences());
                    w.setColHidden(51, h.isAssociatedsequences()==1);
                    w.setColHidden(51 + sp, h.isAssociatedsequences()==1);
                    w.setText("BA"+l,aux.getSp1().getOcc().getOccurrencedetails());
                    w.setColHidden(52, h.isOccurrencedetails()==1);
                    w.setColHidden(52 + sp, h.isOccurrencedetails()==1);
                    w.setText("BB"+l,aux.getSp1().getOcc().getOccurrenceremarks());
                    w.setColHidden(53, h.isOccurrenceremarks()==1);
                    w.setColHidden(53 + sp, h.isOccurrenceremarks()==1);
                    w.setText("BC"+l,aux.getSp1().getOcc().getOccurrencestatus());
                    w.setColHidden(54, h.isOccurrencestatus()==1);
                    w.setColHidden(54 + sp, h.isOccurrencestatus()==1);
                    //Identification Elements
                    w.setText("BD"+l,aux.getSp1().getId().getDateidentified());
                    w.setColHidden(55, h.isDateidentified()==1);
                    w.setColHidden(55 + sp, h.isDateidentified()==1);
                    w.setText("BE"+l,aux.getSp1().getId().getIdentificationqualifier());
                    w.setColHidden(56, h.isIdentificationqualifier()==1);
                    w.setColHidden(56 + sp, h.isIdentificationqualifier()==1);
                    w.setText("BF"+l,aux.getSp1().getId().getIdentifiedby());
                    w.setColHidden(57, h.isIdentifiedby()==1);
                    w.setColHidden(57 + sp, h.isIdentifiedby()==1);
                    w.setText("BG"+l,aux.getSp1().getId().getTypestatus());
                    w.setColHidden(58, h.isTypestatus()==1);
                    w.setColHidden(58 + sp, h.isTypestatus()==1);
                    w.setText("BH"+l,aux.getSp1().getId().getIdentificationremarks());
                    w.setColHidden(59, h.isIdentificationremarks()==1);
                    w.setColHidden(59 + sp, h.isIdentificationremarks()==1);
                    //Event Elements
                    w.setText("BI"+l,aux.getSp1().getEve().getSamplingprotocol());
                    w.setColHidden(60, h.isSamplingprotocol()==1);
                    w.setColHidden(60 + sp, h.isSamplingprotocol()==1);
                    w.setText("BJ"+l,aux.getSp1().getEve().getSamplingeffort());
                    w.setColHidden(61, h.isSamplingeffort()==1);
                    w.setColHidden(61 + sp, h.isSamplingeffort()==1);
                    w.setText("BK"+l,aux.getSp1().getEve().getHabitat());
                    w.setColHidden(62, h.isHabitat()==1);
                    w.setColHidden(62 + sp, h.isHabitat()==1);
                    w.setText("BL"+l,aux.getSp1().getEve().getVerbatimeventdate());
                    w.setColHidden(63, h.isVerbatimeventdate()==1);
                    w.setColHidden(63 + sp, h.isVerbatimeventdate()==1);
                    w.setText("BM"+l,aux.getSp1().getEve().getEventtime());
                    w.setColHidden(64, h.isEventtime()==1);
                    w.setColHidden(64 + sp, h.isEventtime()==1);
                    w.setText("BN"+l,aux.getSp1().getEve().getEventdate());
                    w.setColHidden(65, h.isEventdate()==1);
                    w.setColHidden(65 + sp, h.isEventdate()==1);
                    w.setText("BO"+l,aux.getSp1().getEve().getFieldnumber());
                    w.setColHidden(66, h.isFieldnumber()==1);
                    w.setColHidden(66 + sp, h.isFieldnumber()==1);
                    w.setText("BP"+l,aux.getSp1().getEve().getFieldnotes());
                    w.setColHidden(67, h.isFieldnotes()==1);
                    w.setColHidden(67 + sp, h.isFieldnotes()==1);
                    w.setText("BQ"+l,aux.getSp1().getEve().getEventremarks());
                    w.setColHidden(68, h.isEventremarks()==1);
                    w.setColHidden(68 + sp, h.isEventremarks()==1);
                    //Locality Elements
                    w.setText("BT"+l,aux.getSp1().getLoc().getContinent());
                    w.setColHidden(71, h.isContinent()==1);
                    w.setColHidden(71 + sp, h.isContinent()==1);
                    w.setText("BU"+l,aux.getSp1().getLoc().getCountry());
                    w.setColHidden(72, h.isCountry()==1);
                    w.setColHidden(72 + sp, h.isCountry()==1);
                    w.setText("BV"+l,aux.getSp1().getLoc().getStateorprovince());
                    w.setColHidden(73, h.isStateorprovince()==1);
                    w.setColHidden(73 + sp, h.isStateorprovince()==1);
                    w.setText("BW"+l,aux.getSp1().getLoc().getCounty());
                    w.setColHidden(74, h.isCounty()==1);
                    w.setColHidden(74 + sp, h.isCounty()==1);
                    w.setText("BX"+l,aux.getSp1().getLoc().getMunicipality());                        
                    w.setColHidden(75, h.isMunicipality()==1);
                    w.setColHidden(75 + sp, h.isMunicipality()==1);
                    w.setText("BY"+l,aux.getSp1().getLoc().getLocality());
                    w.setColHidden(76, h.isLocality()==1);
                    w.setColHidden(76 + sp, h.isLocality()==1);
                    w.setText("BZ"+l,aux.getSp1().getLoc().getWaterbody());
                    w.setColHidden(77, h.isWaterbody()==1);
                    w.setColHidden(77 + sp, h.isWaterbody()==1);
                    w.setText("CA"+l,aux.getSp1().getLoc().getIslandgroup());
                    w.setColHidden(78, h.isIslandgroup()==1);
                    w.setColHidden(78 + sp, h.isIslandgroup()==1);
                    w.setText("CB"+l,aux.getSp1().getLoc().getIsland());
                    w.setColHidden(79, h.isIsland()==1);
                    w.setColHidden(79 + sp, h.isIsland()==1);
                    w.setText("CC"+l,aux.getSp1().getLoc().getLocationaccordingto());
                    w.setColHidden(80, h.isLocationaccordingto()==1);
                    w.setColHidden(80 + sp, h.isLocationaccordingto()==1);
                    w.setText("CD"+l,aux.getSp1().getLoc().getCoordinateprecision());
                    w.setColHidden(81, h.isCoordinateprecision()==1);
                    w.setColHidden(81 + sp, h.isCoordinateprecision()==1);
                    w.setText("CE"+l,aux.getSp1().getLoc().getLocationremarks());
                    w.setColHidden(82, h.isLocationremarks()==1);
                    w.setColHidden(82 + sp, h.isLocationremarks()==1);
                    w.setText("CF"+l,aux.getSp1().getLoc().getMinimunelevation());
                    w.setColHidden(83, h.isMinimunelevation()==1);
                    w.setColHidden(83 + sp, h.isMinimunelevation()==1);
                    w.setText("CG"+l,aux.getSp1().getLoc().getMaximumelevation());
                    w.setColHidden(84, h.isMaximumelevation()==1);
                    w.setColHidden(84 + sp, h.isMaximumelevation()==1);
                    w.setText("CH"+l,aux.getSp1().getLoc().getMinimumdepth());
                    w.setColHidden(85, h.isMinimumdepth()==1);
                    w.setColHidden(85 + sp, h.isMinimumdepth()==1);
                    w.setText("CI"+l,aux.getSp1().getLoc().getMaximumdepth());
                    w.setColHidden(86, h.isMaximumdepth()==1);
                    w.setColHidden(86 + sp, h.isMaximumdepth()==1);
                    if(aux.getSp1().getLoc().getMinimumdistance()!=null)
                        w.setNumber("CJ"+l,aux.getSp1().getLoc().getMinimumdistance());
                    w.setColHidden(87, h.isMinimumdistance()==1);
                    w.setColHidden(87 + sp, h.isMinimumdistance()==1);
                    if(aux.getSp1().getLoc().getMaximumdistance()!=null)
                        w.setNumber("CK"+l,aux.getSp1().getLoc().getMaximumdistance());
                    w.setColHidden(88, h.isMinimumdistance()==1);
                    w.setColHidden(88 + sp, h.isMinimumdistance()==1);
                    w.setText("CL"+l,aux.getSp1().getLoc().getVerbatimdepth());
                    w.setColHidden(89, h.isVerbatimdepth()==1);
                    w.setColHidden(89 + sp, h.isVerbatimdepth()==1);
                    w.setText("CM"+l,aux.getSp1().getLoc().getVerbatimelevation());
                    w.setColHidden(90, h.isVerbatimelevation()==1);
                    w.setColHidden(90 + sp, h.isVerbatimelevation()==1);
                    w.setText("CN"+l,aux.getSp1().getLoc().getVerbatimlocality());
                    w.setColHidden(91, h.isVerbatimlocality()==1);
                    w.setColHidden(91 + sp, h.isVerbatimlocality()==1);
                    w.setText("CO"+l,aux.getSp1().getLoc().getVerbatimsrs());
                    w.setColHidden(92, h.isVerbatimsrs()==1);
                    w.setColHidden(92 + sp, h.isVerbatimsrs()==1);
                    w.setText("CP"+l,aux.getSp1().getLoc().getGeoreferencedby());
                    w.setColHidden(93, h.isGeoreferencedby()==1);
                    w.setColHidden(93 + sp, h.isGeoreferencedby()==1);
                    w.setText("CQ"+l,aux.getSp1().getLoc().getGeoreferencesources());
                    w.setColHidden(94, h.isGeoreferencesources()==1);
                    w.setColHidden(94 + sp, h.isGeoreferencesources()==1);
                    w.setText("CR"+l,aux.getSp1().getLoc().getFootprintsrs());
                    w.setColHidden(95, h.isFootprintsrs()==1);
                    w.setColHidden(95 + sp, h.isFootprintsrs()==1);
                    //Geospatial Elements
                    if(aux.getSp1().getGeo().getDecimalaltitude()!=null)
                        w.setNumber("BR"+l,aux.getSp1().getGeo().getDecimalaltitude());
                    w.setColHidden(69, h.isDecimalaltitude()==1);
                    w.setColHidden(69 + sp, h.isDecimalaltitude()==1);
                    if(aux.getSp1().getGeo().getDecimallongitude()!=null)
                    w.setNumber("BS"+l,aux.getSp1().getGeo().getDecimallongitude());
                    w.setColHidden(70, h.isDecimallongitude()==1);
                    w.setColHidden(70 + sp, h.isDecimallongitude()==1);
                    if(aux.getSp1().getGeo().getCoordinateuncertainty()!=null)
                        w.setNumber("CS"+l,aux.getSp1().getGeo().getCoordinateuncertainty());
                    w.setColHidden(96, h.isCoordinateuncertainty()==1);
                    w.setColHidden(96 + sp, h.isCoordinateuncertainty()==1);
                    w.setText("CT"+l,aux.getSp1().getGeo().getGeodeticdatum());
                    w.setColHidden(97, h.isGeodeticdatum()==1);
                    w.setColHidden(97 + sp, h.isGeodeticdatum()==1);
                    w.setText("CU"+l,aux.getSp1().getGeo().getPointradiusspatialfit());
                    w.setColHidden(98, h.isPointradiusspatialfit()==1);
                    w.setColHidden(98 + sp, h.isPointradiusspatialfit()==1);
                    w.setText("CV"+l,aux.getSp1().getGeo().getVerbatimcoordinates());
                    w.setColHidden(99, h.isVerbatimcoordinates()==1);
                    w.setColHidden(99 + sp, h.isVerbatimcoordinates()==1);
                    w.setText("CW"+l,aux.getSp1().getGeo().getVerbatimlatitude());
                    w.setColHidden(100, h.isVerbatimlatitude()==1);
                    w.setColHidden(100 + sp, h.isVerbatimlatitude()==1);
                    w.setText("CX"+l,aux.getSp1().getGeo().getVerbatimlongitude());
                    w.setColHidden(101, h.isVerbatimlongitude()==1);
                    w.setColHidden(101 + sp, h.isVerbatimlongitude()==1);
                    w.setText("CY"+l,aux.getSp1().getGeo().getVerbatimcoordinatesystem());
                    w.setColHidden(102, h.isVerbatimcoordinatesystem()==1);
                    w.setColHidden(102 + sp, h.isVerbatimcoordinatesystem()==1);
                    w.setText("CZ"+l,aux.getSp1().getGeo().getGeoreferenceprotocol());
                    w.setColHidden(103, h.isGeoreferenceprotocol()==1);
                    w.setColHidden(103 + sp, h.isGeoreferenceprotocol()==1);
                    w.setText("DA"+l,aux.getSp1().getGeo().getVerificationstatus());
                    w.setColHidden(104, h.isVerificationstatus()==1);
                    w.setColHidden(104 + sp, h.isVerificationstatus()==1);
                    w.setText("DB"+l,aux.getSp1().getGeo().getGeoreferenceremarks());
                    w.setColHidden(105, h.isGeoreferenceremarks()==1);
                    w.setColHidden(105 + sp, h.isGeoreferenceremarks()==1);
                    w.setText("DC"+l,aux.getSp1().getGeo().getFootprintwkt());
                    w.setColHidden(106, h.isFootprintwkt()==1);
                    w.setColHidden(106 + sp, h.isFootprintwkt()==1);
                    w.setText("DD"+l,aux.getSp1().getGeo().getFootprintspatialfit());
                    w.setColHidden(107, h.isFootprintspatialfit()==1);
                    w.setColHidden(107 + sp, h.isFootprintspatialfit()==1);
           
                    // se houver interação, cadastrar interação e sp2 tbm
                    if(aux.getInte()!=null){
                        //Interaction
                        w.setText("DE"+l, aux.getInte().getInteractiontype());
                        w.setText("DF"+l, aux.getInte().getInteractioninformation());
                        //Main Fields
                        w.setText("DH"+l, aux.getSp2().getPrivate1()?"YES":"NO");
                        w.setText("DI" + l, aux.getSp2().getMain().getBasisofrecord());
                        w.setText("DJ" + l, aux.getSp2().getMain().getInstitutioncode());
                        w.setText("DK" + l, aux.getSp2().getMain().getCollectioncode());
                        w.setText("DL" + l, aux.getSp2().getMain().getCatalognumber());
                        w.setText("DM" + l, aux.getSp2().getMain().getScientificname());
                        //Record-Level Elements
                        w.setText("DN" + l, aux.getSp2().getRec().getType());
                        w.setText("DO" + l, aux.getSp2().getRec().getOwnercode());
                        w.setText("DP" + l, aux.getSp2().getRec().getDataset());
                        w.setText("DQ" + l, aux.getSp2().getRec().getRights());
                        w.setText("DR" + l, aux.getSp2().getRec().getRightsholder());
                        w.setText("DS" + l, aux.getSp2().getRec().getAccessrights());
                        w.setText("DT" + l, aux.getSp2().getRec().getInformationwithheld());
                        w.setText("DU" + l, aux.getSp2().getRec().getDatageneralization());
                        w.setText("DV" + l, aux.getSp2().getRec().getDynamicproperties());
                        //Taxonomic Elements
                        w.setText("DW" + l, aux.getSp2().getTax().getKingdom());
                        w.setText("DX" + l, aux.getSp2().getTax().getPhylum());
                        w.setText("DY" + l, aux.getSp2().getTax().getClasse());
                        w.setText("DZ" + l, aux.getSp2().getTax().getOrder());
                        w.setText("EA" + l, aux.getSp2().getTax().getFamily());
                        w.setText("EB" + l, aux.getSp2().getTax().getGenus());
                        w.setText("EC" + l, aux.getSp2().getTax().getSubgenus());
                        w.setText("ED" + l, aux.getSp2().getTax().getSpecificepithet());
                        w.setText("EE" + l, aux.getSp2().getTax().getInfraspecificepithet());
                        w.setText("EF" + l, aux.getSp2().getTax().getTaxonrank());
                        w.setText("EG" + l, aux.getSp2().getTax().getScientificnameauthor());
                        w.setText("EH" + l, aux.getSp2().getTax().getNomenclaturalcode());
                        w.setText("EI" + l, aux.getSp2().getTax().getTaxonconcept());
                        w.setText("EJ" + l, aux.getSp2().getTax().getNomenclaturalstatus());
                        w.setText("EK" + l, aux.getSp2().getTax().getAcceptednameusage());
                        w.setText("EL" + l, aux.getSp2().getTax().getParentnameusage());
                        w.setText("EM" + l, aux.getSp2().getTax().getOriginalnameusage());
                        w.setText("EN" + l, aux.getSp2().getTax().getNameaccordingto());
                        w.setText("EO" + l, aux.getSp2().getTax().getNamepublishedin());
                        w.setText("EP" + l, aux.getSp2().getTax().getVernacularname());
                        w.setText("EQ" + l, aux.getSp2().getTax().getTaxonomicstatus());
                        w.setText("ER" + l, aux.getSp2().getTax().getVerbatimtaxonrank());
                        w.setText("ES" + l, aux.getSp2().getTax().getTaxonremarks());
                        //Occurrence Elements
                        w.setText("ET" + l, aux.getSp2().getOcc().getIndividual());
                        if(aux.getSp2().getOcc().getIndividualcount()!=null)
                            w.setNumber("EU" + l, aux.getSp2().getOcc().getIndividualcount());
                        w.setText("EV" + l, aux.getSp2().getOcc().getSex());
                        w.setText("EW" + l, aux.getSp2().getOcc().getBehavior());
                        w.setText("EX" + l, aux.getSp2().getOcc().getLifestage());
                        w.setText("EY" + l, aux.getSp2().getOcc().getDisposition());
                        w.setText("EZ" + l, aux.getSp2().getOcc().getReproductivecondition());
                        w.setText("FA" + l, aux.getSp2().getOcc().getEstabilishmentmean());
                        w.setText("FB" + l, aux.getSp2().getOcc().getRecordedby());
                        w.setText("FC" + l, aux.getSp2().getOcc().getRecordnumber());
                        w.setText("FD" + l, aux.getSp2().getOcc().getOthercatalognumbers());
                        w.setText("FE" + l, aux.getSp2().getOcc().getPreparations());
                        w.setText("FF" + l, aux.getSp2().getOcc().getAssociatedsequences());
                        w.setText("FG" + l, aux.getSp2().getOcc().getOccurrencedetails());
                        w.setText("FH" + l, aux.getSp2().getOcc().getOccurrenceremarks());
                        w.setText("FI" + l, aux.getSp2().getOcc().getOccurrencestatus());
                        //Identification Elements
                        w.setText("FJ" + l, aux.getSp2().getId().getDateidentified());
                        w.setText("FK" + l, aux.getSp2().getId().getIdentificationqualifier());
                        w.setText("FL" + l, aux.getSp2().getId().getIdentifiedby());
                        w.setText("FM" + l, aux.getSp2().getId().getTypestatus());
                        w.setText("FN" + l, aux.getSp2().getId().getIdentificationremarks());
                        //Event Elements
                        w.setText("FO" + l, aux.getSp2().getEve().getSamplingprotocol());
                        w.setText("FP" + l, aux.getSp2().getEve().getSamplingeffort());
                        w.setText("FQ" + l, aux.getSp2().getEve().getHabitat());
                        w.setText("FR" + l, aux.getSp2().getEve().getVerbatimeventdate());
                        w.setText("FS" + l, aux.getSp2().getEve().getEventtime());
                        w.setText("FT" + l, aux.getSp2().getEve().getEventdate());
                        w.setText("FU" + l, aux.getSp2().getEve().getFieldnumber());
                        w.setText("FV" + l, aux.getSp2().getEve().getFieldnotes());
                        w.setText("FW" + l, aux.getSp2().getEve().getEventremarks());
                        //Locality Elements
                        
                        w.setText("FZ" + l, aux.getSp2().getLoc().getContinent());
                        w.setText("GA" + l, aux.getSp2().getLoc().getCountry());
                        w.setText("GB" + l, aux.getSp2().getLoc().getStateorprovince());
                        w.setText("GC" + l, aux.getSp2().getLoc().getCounty());
                        w.setText("GD" + l, aux.getSp2().getLoc().getMunicipality());
                        w.setText("GE" + l, aux.getSp2().getLoc().getLocality());
                        w.setText("GF" + l, aux.getSp2().getLoc().getWaterbody());
                        w.setText("GG" + l, aux.getSp2().getLoc().getIslandgroup());
                        w.setText("GH" + l, aux.getSp2().getLoc().getIsland());
                        w.setText("GI" + l, aux.getSp2().getLoc().getLocationaccordingto());
                        w.setText("GJ" + l, aux.getSp2().getLoc().getCoordinateprecision());
                        w.setText("GK" + l, aux.getSp2().getLoc().getLocationremarks());
                        w.setText("GL" + l, aux.getSp2().getLoc().getMinimunelevation());
                        w.setText("GM" + l, aux.getSp2().getLoc().getMaximumelevation());
                        w.setText("GN" + l, aux.getSp2().getLoc().getMinimumdepth());
                        w.setText("GO" + l, aux.getSp2().getLoc().getMaximumdepth());
                        if(aux.getSp2().getLoc().getMinimumdistance()!=null)
                            w.setNumber("GP" + l, aux.getSp2().getLoc().getMinimumdistance());
                        if(aux.getSp2().getLoc().getMaximumdistance()!=null)
                            w.setNumber("GQ" + l, aux.getSp2().getLoc().getMaximumdistance());
                        w.setText("GR" + l, aux.getSp2().getLoc().getVerbatimdepth());
                        w.setText("GS" + l, aux.getSp2().getLoc().getVerbatimelevation());
                        w.setText("GT" + l, aux.getSp2().getLoc().getVerbatimlocality());
                        w.setText("GU" + l, aux.getSp2().getLoc().getVerbatimsrs());
                        w.setText("GV" + l, aux.getSp2().getLoc().getGeoreferencedby());
                        w.setText("GW" + l, aux.getSp2().getLoc().getGeoreferencesources());
                        w.setText("GX" + l, aux.getSp2().getLoc().getFootprintsrs());
                        //Geospatial Elements
                        if(aux.getSp2().getGeo().getDecimalaltitude()!=null)
                            w.setNumber("FX" + l, aux.getSp2().getGeo().getDecimalaltitude());
                        if(aux.getSp2().getGeo().getDecimallongitude()!=null)
                            w.setNumber("FY" + l, aux.getSp2().getGeo().getDecimallongitude());
                        System.out.println(aux.getSp2().getGeo().getCoordinateuncertainty());
                        if(aux.getSp2().getGeo().getCoordinateuncertainty()!=null)
                            w.setNumber("GY" + l, aux.getSp2().getGeo().getCoordinateuncertainty());
                        w.setText("GZ" + l, aux.getSp2().getGeo().getGeodeticdatum());
                        w.setText("HA" + l, aux.getSp2().getGeo().getPointradiusspatialfit());
                        w.setText("HB" + l, aux.getSp2().getGeo().getVerbatimcoordinates());
                        w.setText("HC" + l, aux.getSp2().getGeo().getVerbatimlatitude());
                        w.setText("HD" + l, aux.getSp2().getGeo().getVerbatimlongitude());
                        w.setText("HE" + l, aux.getSp2().getGeo().getVerbatimcoordinatesystem());
                        w.setText("HF" + l, aux.getSp2().getGeo().getGeoreferenceprotocol());
                        w.setText("HG" + l, aux.getSp2().getGeo().getVerificationstatus());
                        w.setText("HH" + l, aux.getSp2().getGeo().getGeoreferenceremarks());
                        w.setText("HI" + l, aux.getSp2().getGeo().getFootprintwkt());
                        w.setText("HJ" + l, aux.getSp2().getGeo().getFootprintspatialfit());
                    }
                    l++;
            }
        } catch (Exception ex) {
            Logger.getLogger(SpreadsheetHandling.class.getName()).log(Level.SEVERE, null, ex);
        }
        // Grava aquivo preenchido
        try {
            Date date = new Date();
            SimpleDateFormat dateformatMMDDYYYYkmmss = new SimpleDateFormat("_MM-dd-yyyy_k-mm-ss");

            StringBuilder nowMMDDYYYYkmmss = new StringBuilder( dateformatMMDDYYYYkmmss.format( date ) );

            String target = "bdd_sheet"+nowMMDDYYYYkmmss+".xls";
            //String target = "/var/www/bdd/images/uploaded/bdd_sheet"+nowMMDDYYYYkmmss+".xls";
            w.write(target);            
            return "http://bdd.pcs.usp.br/bdd/images/uploaded/bdd_sheet"+nowMMDDYYYYkmmss+".xls";
        } catch (Exception ex) {
            Logger.getLogger(SpreadsheetHandling.class.getName()).log(Level.SEVERE, null, ex);
        }
        return "";
    }
}
